﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace BusinessLayer
{
    public class Borrower
    {
        private string _personId, _Name, _address, _telno, _firstName, _lastName;
        private int _categoryId;

        public string PersonId { get { return this._personId; } set { this._personId = value; } }
        public string Name { get { return this._Name; } set { this._Name = value; } }
        public string Address { get { return this._address; } set { this._address = value; } }
        public string Telno { get { return this._telno; } set { this._telno = value; } }
        public int CategoryId { get { return this._categoryId; } set { this._categoryId = value; } }

        public string FirstName { get { return this._firstName; } set { this._firstName = value; } }
        public string LastName { get { return this._lastName; } set { this._lastName = value; } }



        public static Borrower getBorrowerByID(string persID)
        {
            string sql =
                "SELECT BORROWER.PersonId, BORROWER.Address, BORROWER.CategoryId, BORROWER.TelNo, BORROWER.FirstName, BORROWER.LastName FROM BORROWER " +
                "WHERE BORROWER.PersonId=@PID";
            Borrower newBorrower = new Borrower();;
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("PID", persID));
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                if (dar.Read())
                {
                    newBorrower.PersonId = dar["PersonId"] as string;
                    newBorrower.Address = dar["Address"] as string;
                    newBorrower.FirstName = dar["FirstName"] as string;
                    newBorrower.LastName = dar["LastName"] as string;
                    newBorrower.Telno = dar["TelNo"] as string;
                    newBorrower.CategoryId = (int)dar["CategoryId"];
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return newBorrower;
        }

        public static List<Borrower> getBorrowerByName(string name)
        {            
            string sql =
                "SELECT BORROWER.PersonId, BORROWER.Address, BORROWER.CategoryId, BORROWER.TelNo, BORROWER.FirstName + ' ' + BORROWER.LastName AS BorrowerName FROM BORROWER " +
                "WHERE BORROWER.FirstName + ' ' + BORROWER.LastName LIKE '%" + name + "%' ";

            List<Borrower> result = new List<Borrower>();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Borrower newBorrower = new Borrower();
                    newBorrower.PersonId = dar["PersonId"] as string;
                    newBorrower.Address = dar["Address"] as string;
                    newBorrower.Name = dar["BorrowerName"] as string;
                    newBorrower.Telno = dar["TelNo"] as string;
                    newBorrower.CategoryId = (int)dar["CategoryId"];
                    result.Add(newBorrower);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        //public static List<Book> getLoans(string persID)
        //{
        //    List<Book> result = new List<Book>();
        //    string sql = "SELECT BORROW.Barcode, BORROW.PersonId, BORROW.BorrowDate, BORROW.ToBeReturnedDate, BORROW.ReturnDate, COPY.Barcode, BOOK.ISBN, BOOK.Title, BOOK.SignId, BOOK.PublicationYear, BOOK.Publisher, BOOK.LibNo " +
        //        "FROM BORROWER INNER JOIN BORROW ON BORROWER.PersonId = BORROW.PersonId INNER JOIN COPY ON BORROW.Barcode = COPY.Barcode INNER JOIN BOOK ON COPY.ISBN = BOOK.ISBN " +
        //        "WHERE (BORROWER.PersonId = '" + persID + "')";

        //    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
        //    SqlCommand cmd = new SqlCommand(sql, con);
        //    try
        //    {
        //        con.Open();
        //        SqlDataReader dar = cmd.ExecuteReader();
        //        while (dar.Read())
        //        {
        //            Book newBook = new Book();
        //            newBook.Barcode = dar["Barcode"] as string;
        //            newBook.ISBN = dar["ISBN"] as string;
        //            newBook.Title = dar["Title"] as string;
        //            result.Add(newBook);
        //        }
        //    }
        //    catch (Exception er)
        //    {
        //        throw er;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }

        //    return result;
        //}

        public static void AddBorrower(string PersonId, string FirstName, string LastName, string Address, string Telno, int CategoryId)
        {
            string sql = "INSERT INTO dbo.BORROWER (PersonId, FirstName, LastName, Address, Telno, CategoryId) VALUES (@PersonId,@FirstName,@LastName,@Address,@Telno,@CategoryId)";

            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand(sql, dbCon);
            dbCmd.Parameters.Add(new SqlParameter("PersonId", PersonId));
            dbCmd.Parameters.Add(new SqlParameter("FirstName", FirstName));
            dbCmd.Parameters.Add(new SqlParameter("LastName", LastName));
            dbCmd.Parameters.Add(new SqlParameter("Address", Address));
            dbCmd.Parameters.Add(new SqlParameter("Telno", Telno));
            dbCmd.Parameters.Add(new SqlParameter("CategoryId", CategoryId));

            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }
        
        }
        public static void ModBorrower(string PersonId, string FirstName, string LastName, string Address, string Telno, int CategoryId)
        {
            string sql = "UPDATE dbo.BORROWER SET FirstName=@FirstName, LastName=@LastName, Address=@Address, Telno=@Telno, CategoryId=@CategoryId WHERE PersonId=@PersonId";

            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand(sql, dbCon);

            dbCmd.Parameters.Add(new SqlParameter("PersonId", PersonId));
            dbCmd.Parameters.Add(new SqlParameter("FirstName", FirstName));
            dbCmd.Parameters.Add(new SqlParameter("LastName", LastName));
            dbCmd.Parameters.Add(new SqlParameter("Address", Address));
            dbCmd.Parameters.Add(new SqlParameter("Telno", Telno));
            dbCmd.Parameters.Add(new SqlParameter("CategoryId", CategoryId));

            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }

        }
        public static void RemoveBorrower(string PersonId)
        {
            string sql = "DELETE FROM BORROW WHERE BORROW.PersonId=@PersonId; DELETE FROM BORROWER WHERE BORROWER.PersonId=@PersonId; DELETE FROM USERS WHERE USERS.PersonId=@PersonId; ";
            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand(sql, dbCon);

            dbCmd.Parameters.Add(new SqlParameter("PersonId", PersonId));

            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }

        }

    }
}
